/*
* JDBCAdapter.java v_0.01 2010/9/06
*
* Copyright (c) 2010 Jong Sung Lee.
* Medical Infomatics Laboratory (718 Ho), Basic Research Building, 
* College of Medicine Seoul National Univ, Yeongeon-dong, Jongno-gu, 
* Seoul, Korea (Seoul 110-799 Korea)
* All rights reserved.
*
* This software is the confidential and proprietary information of Medical Infomatics 
* Laboratory ("Confidential Information"). You shall not disclose such Confidential
* Information and shall use it only in accordance with the terms of the license
* agreement you entered into with Medinfo.
*/

package kr.ac.snu.medinfo.edc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

//import

/**
* JDBCAdapter
* 설명 : JDBC의 연동부분을 담당하고 있는 클래스
* SubjectManager 연동 완료
* TestManager 연동완료
* ResearchManager 연동완료
* UserManager 연동완료
* ApprovalManager 연동중!
* 
* 크게 JDBCAdapter가 하는일은 각  Manager 로부터 받은 일을 database에서 가져오는 일을 수행함.
* 또한 MssqlManager로부터 Mssql에 최적화된 SQL 쿼리를 가져와서 database에 명령을 내리고,
* 결과를 받아서 돌려준다.
*
* @version 0.01 06 Sep 2010
* @author Jong Sung Lee
*/

public abstract class JDBCAdapter {
	
	private Connection databaseConnection; //데이터베이스 연결정보를 가지는 변수
	protected String connectionString; //jdbc:sqlserver://147.46.170.221:1433 값을 가지고 있음.
	protected String DBUserId; //데이터베이스 사용자 아이디
	protected String DBPassword; //데이터베이스 사용자 비밀번호

	public void disconnect() throws SQLException { //DB연결을 끊을 때 사용.
		databaseConnection.close();
	}

	//DB 연결시 사용.
	public void connect() throws ClassNotFoundException, SQLException {
		databaseConnection = null;

		loadDriver(); //드라이버를 로드함.
		
		databaseConnection = DriverManager.getConnection(connectionString,
				DBUserId, DBPassword);
	}
	
	//=======================================================================
	//Approvals Table
	public void insertApprovals(Approval approval){
		
			String sql = getSQLforInsertApprovals(approval); //SQL 미작성부분.
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in insertApprovals!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	public User readApprovals(Approval approval){
		String sql = getSQLforReadApprovals(approval);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		User user1 = new User();
		try {
			stmt = databaseConnection.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while(rs.next()){
				user1.setName(rs.getString(1));
				user1.setUserId(rs.getString(2));
				user1.setPassword(rs.getString(3));
				user1.setSSN(rs.getString(4)); 
				user1.setEmail(rs.getString(5));
				user1.setPhoneNumber(rs.getString(6));
				user1.setMobileNumber(rs.getString(7));
				user1.setNumberOfMessage(rs.getInt(8));
			}
			
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException Occured in insertApprovals!");
		} finally {
			closeResultSetAndStatement(rs,stmt);
		}
		
		return user1;
	}
	
	public void updateApprovals(Approval approval){
		
			String sql = getSQLforUpdateApprovals(approval);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in insertApprovals!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	public void deleteApprovals(Approval approval){
		
			String sql = getSQLforDeleteApprovals(approval);
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in deleteApprovals!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	//=======================================================================
	//Users Table
	public void insertUsers(User user){
		
			String sql = getSQLforInsertUsers(user);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in insertUsers!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	public User readUsers(User user){
		String sql = getSQLforReadUsers(user);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		User user1 = new User();
		try {
			stmt = databaseConnection.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while(rs.next()){
				user1.setName(rs.getString(1));
				user1.setUserId(rs.getString(2));
				user1.setPassword(rs.getString(3));
				user1.setSSN(rs.getString(4)); 
				user1.setEmail(rs.getString(5));
				user1.setPhoneNumber(rs.getString(6));
				user1.setMobileNumber(rs.getString(7));
				user1.setNumberOfMessage(rs.getInt(8));
			}
			
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException Occured in readUsers!");
		} finally {
			closeResultSetAndStatement(rs,stmt);
		}
		
		return user1;
	}
	
	public void updateUsers(User user){
		
			String sql = getSQLforUpdateUsers(user);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in updateUsers!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	public void deleteUsers(User user){
		
			String sql = getSQLforDeleteUsers(user);
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in deleteUsers!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	//=======================================================================
	//Researches Table
	public void insertResearches(String ResearchId, String ResearchName, 
			String StartingTime, String ExpectedFinishTime){
		
			String sql = getSQLforInsertResearches(ResearchId, ResearchName, 
					StartingTime, ExpectedFinishTime);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in insertResearches!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	public Research readResearches(String ResearchId){
		String sql = getSQLforReadResearches(ResearchId);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Research research = new Research();
		try {
			
			stmt = databaseConnection.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while(rs.next()){
				research.setResearchId(rs.getString(1));
				research.setResearchName(rs.getString(2));
				research.setStartingTime(rs.getString(3));
				research.setExpectedFinishTime(rs.getString(4));
			}
			
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException Occured in readResearches!");
		} finally {
			closeResultSetAndStatement(rs,stmt);
		}
		
		return research;
	}
	
	public void updateResearches(String ResearchId, String ResearchName, 
			String StartingTime, String ExpectedFinishTime){
		
			String sql = getSQLforUpdateResearches(ResearchId, ResearchName, 
					StartingTime, ExpectedFinishTime);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in updateResearches!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	public void deleteResearches(String ResearchId){
		
			String sql = getSQLforDeleteResearches(ResearchId);
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in deleteResearches!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	//=======================================================================
	//Tests Table
	
	public Test readTests(String TestId){
		String sql = getSQLforReadTests(TestId);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Test test = new Test();
		try {
			stmt = databaseConnection.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while(rs.next()){
				test.setTestId(rs.getString(1));
				test.setCRF_Name(rs.getString(2));
				test.setCRF_TestCode(rs.getString(3));
				test.setEMR_Name(rs.getString(4));
				test.setEMR_TestCode(rs.getString(5));
			}
			
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException Occured in readResearches!");
		} finally {
			closeResultSetAndStatement(rs,stmt);
		}
		
		return test;
	}
	
	public void insertTests(String TestId, String CRF_Name, 
			String CRF_TestCode, String EMR_Name, String EMR_TestCode){
		
			String sql = getSQLforInsertTests(TestId, CRF_Name, CRF_TestCode, 
					EMR_Name, EMR_TestCode);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in insertTests!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	public void deleteTests(String TestId){
		
		String sql = getSQLforDeleteTests(TestId);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = databaseConnection.prepareStatement(sql);
			stmt.executeUpdate();
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException Occured in deleteTests!");
		} finally {
			closeResultSetAndStatement(rs,stmt);
		}
	}
	
	//Tests 테이블의 정보를 update 할 때 사용되는메소드
	public void updateTests(String TestId, String CRF_Name, 
			String CRF_TestCode, String EMR_Name, String EMR_TestCode){
		
			String sql = getSQLforUpdateTests(TestId, CRF_Name, CRF_TestCode, 
					EMR_Name, EMR_TestCode);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in updateTests!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	//=======================================================================
	//Subjects Table
	
	//Subjects 테이블의 정보를 read 할 때 사용되는메소드
	//여기서 SSN만 받은 이유는 SSN이 Key역할을 하기 때문에 SSN 만으로 사용자 정보를 모두 가져올 수 있기때문.
	public Subject readSubjects(String SSN){
		String sql = getSQLforReadSubjects(SSN);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Subject subject = new Subject();
		try {
			stmt = databaseConnection.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while(rs.next()){
				subject.setSSN(rs.getString(1));
				subject.setName(rs.getString(2));
				subject.setCRF_SubjectID(rs.getString(3));
				subject.setEMR_PID(rs.getString(4));
				subject.setEnrolledTime(rs.getString(5));
			}
			
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException Occured in readSubjects!");
		} finally {
			closeResultSetAndStatement(rs,stmt);
		}
		
		return subject;
	}
	
	//Subjects 테이블의 정보를 Insert할 때 사용되는메소드
	public void insertSubjects(String SSN, String Name, 
			String CRF_SubjectID, String EMR_PID, String EnrolledTime){
		
			String sql = getSQLforInsertSubjects(SSN,
					Name, CRF_SubjectID, EMR_PID, EnrolledTime);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in insertSubjects!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	//Subjects 테이블의 정보를 Delete할 때 사용되는메소드
	public void deleteSubjects(String SSN){
		
		String sql = getSQLforDeleteTests(SSN);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = databaseConnection.prepareStatement(sql);
			stmt.executeUpdate();
		}catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException Occured in deleteSubjects!");
		} finally {
			closeResultSetAndStatement(rs,stmt);
		}
	}
	
	//Subjects 테이블의 정보를 Update할 때 사용되는 메소드
	public void updateSubjects(String SSN, String Name, 
			String CRF_SubjectID, String EMR_PID, String EnrolledTime){
		
			String sql = getSQLforUpdateTests(SSN, 
					Name, CRF_SubjectID, EMR_PID, EnrolledTime);
			
			PreparedStatement stmt = null;
			ResultSet rs = null;
			
			try {
				stmt = databaseConnection.prepareStatement(sql);
				stmt.executeUpdate();
			}catch (SQLException e) {
				e.printStackTrace();
				System.out.println("SQLException Occured in updateSubjects!");
			} finally {
				closeResultSetAndStatement(rs,stmt);
			}
	}
	
	//ResultSet을 메모리에서 지우기 위한 메소드
	public void closeResultSetAndStatement(ResultSet rs, PreparedStatement stmt){
		if (rs != null)
			try {rs.close();} catch (Exception e) {}
		if (stmt != null)
			try {stmt.close();} catch (Exception e) {}
	}
	
	protected abstract void loadDriver() throws ClassNotFoundException;
	
	//Approvals
	protected abstract String getSQLforInsertApprovals(Approval approval);
	protected abstract String getSQLforUpdateApprovals(Approval approval);
	protected abstract String getSQLforDeleteApprovals(Approval approval);
	protected abstract String getSQLforReadApprovals(Approval approval);
	
	//Researches
	protected abstract String getSQLforInsertUsers(User user);
	protected abstract String getSQLforUpdateUsers(User user);
	protected abstract String getSQLforDeleteUsers(User user);
	protected abstract String getSQLforReadUsers(User user);
	
	//Researches

	protected abstract String getSQLforInsertResearches(String ResearchId, String ResearchName, 
			String StartingTime, String ExpectedFinishTime);
	protected abstract String getSQLforUpdateResearches(String ResearchId,String ResearchName,
			String StartingTime,String ExpectedFinishTime);
	protected abstract String getSQLforDeleteResearches(String ResearchId);
	protected abstract String getSQLforReadResearches(String ResearchId);
	
	//Tests
	protected abstract String getSQLforInsertTests(String TestId, String CRF_Name, 
			String CRF_TestCode, String EMR_Name, String EMR_TestCode);
	protected abstract String getSQLforUpdateTests(String TestId, String CRF_Name, 
			String CRF_TestCode, String EMR_Name, String EMR_TestCode);
	protected abstract String getSQLforDeleteTests(String TestId);
	protected abstract String getSQLforReadTests(String TestId);
	
	//Subjects
	protected abstract String getSQLforInsertSubjects(String SSN, String Name, 
			String CRF_SubjectID, String EMR_PID, String EnrolledTime);
	protected abstract String getSQLforUpdateSubjects(String SSN, String Name, 
			String CRF_SubjectID, String EMR_PID, String EnrolledTime);
	protected abstract String getSQLforDeleteSubjects(String SSN);
	protected abstract String getSQLforReadSubjects(String SSN);
}
